package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import edzest.model.MemberVO;
import edzest.utils.OracleJDBC;

public class AdoptersDAO {
	private final String insertSQL = "insert into adopters(adono, adoname, address, address_spec, tel, repno, grants) values(adoseq.nextval, ?, ?, ?, ?, ?, ?)";
	private final String updateSQL = "update adopters set adoname = ?, address = ?, address_spec = ?, tel = ?, repno = ?, grants = ?, regdate = sysdate where adono = ?"; 
	private final String deleteSQL = "delete from adopters where adono = ?";
	private final String selectSQL = "select adono, adoname, tel, regdate from adopters order by adono desc";
	private final String selectOneSQL = "select * from adopters where adono = ?";
	
	public int insertAdopter(AdoptersVO ado) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, ado.getAdoname());
				ps.setString(2, ado.getAddress());
				ps.setString(3, ado.getAddress_spec());
				ps.setString(4, ado.getTel());
				ps.setString(5, ado.getRepno());
				ps.setString(6, ado.getGrants());
			cnt = ps.executeUpdate();
		}catch (Exception ex){
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, null); 
		}
		
		return cnt;
	}
	
	public int updateAdopter(AdoptersVO ado) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			conn = OracleJDBC.openConn(); 
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, ado.getAdoname());
				ps.setString(2, ado.getAddress());
				ps.setString(3, ado.getAddress_spec());
				ps.setString(4, ado.getTel());
				ps.setString(5, ado.getRepno());
				ps.setString(6, ado.getGrants());
				ps.setString(7, ado.getAdono());
			cnt = ps.executeUpdate();  // SQL문 실행 후 결과 받기
		} catch (Exception ex){
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, null); 
		}
		
		return cnt;
	}
	
	public int deleteAdopter( String ano ) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			conn = OracleJDBC.openConn(); 
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, ano);
			cnt = ps.executeUpdate();
		} catch (Exception ex){
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, null); 
		}
		
		return cnt;
	}
	
	public ArrayList<AdoptersVO> selectAdopter() {
		ArrayList<AdoptersVO> list = new ArrayList<>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn(); 
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while(rs.next()) {
				AdoptersVO ado = new AdoptersVO(rs.getString(1), rs.getString(2), null, null, rs.getString(3), rs.getString(4), null, null);
				list.add( ado );
			}
		} catch (Exception ex){
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs); 
		}
		return list;
	}
	
	//
	public AdoptersVO selectOneAdopter( String ano ) {
		AdoptersVO a = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn(); 
			ps = conn.prepareStatement(selectOneSQL);
				ps.setString(1, ano);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				a = new AdoptersVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
			}
		} catch (Exception ex){
			ex.printStackTrace();
		} finally {
			OracleJDBC.closeConn(conn, ps, rs); 
		}
		
		return a;
	}
}
